

#!/bin/bash


APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi 






dwd_trade_order_detail_inc="
insert overwrite table $APP.dwd_trade_order_detail_inc partition (dt='$do_date')
select
    od.id,
    order_id,
    user_id,
    course_id,
    province_id,
    coupon_id,
    date_format(create_time, 'yyyy-MM-dd') date_id,
    create_time,
    source_id,
    source_site,
    source_url,
    origin_amount,
    coupon_reduce,
    final_amount
from
(
    select
        data.id,
        data.order_id,
        data.course_id,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
        null source_id,
        null source_site,
        null source_url,
        null coupon_id,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount
    from $APP.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'insert'
) od
left join
(
    select
        data.id,
        data.user_id ,
        data.province_id
    from $APP.ods_order_info_inc
    where dt = '$do_date'
    and type = 'insert'
) oi
on od.order_id = oi.id
;
"


dwd_trade_pay_detail_suc_inc="
insert overwrite table $APP.dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
select
    od.id,
    od.order_id,
    user_id,
    course_id,
    province_id,
    coupon_id,
    payment_type,
    payment_name,
    date_format(callback_time,'yyyy-MM-dd') date_id,
    callback_time,
    source_id,
    source_site,
    source_url,
    origin_amount,
    coupon_reduce,
    final_amount
from
(
    select
        data.id,
        data.order_id,
        data.course_id,
        null coupon_id,
        null source_id,
        null source_site,
        null source_url,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount
    from $APP.ods_order_detail_inc
    where dt = '$do_date'
    and type = 'insert'
) od
join
(
    select
        data.order_id,
        data.payment_type,
        null payment_name,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') callback_time
    from $APP.ods_payment_info_inc
    where dt='$do_date'
    and type='insert'
    and data.payment_status='1602'
) pi
on od.order_id=pi.order_id
left join
(
    select
        data.user_id,
        data.id,
        data.province_id
    from $APP.ods_order_info_inc
    where dt = '$do_date'
    and type = 'insert'
) oi
on od.order_id = oi.id;



"



dwd_interaction_favor_add_inc="
insert overwrite table $APP.dwd_interaction_favor_add_inc partition(dt='$do_date')
select
    data.id,
    data.user_id,
    data.course_id,
    date_format(FROM_UNIXTIME(ts,'yyyy-MM-dd') ,'yyyy-MM-dd') date_id,
    FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time
from $APP.ods_favor_info_inc
where dt='$do_date'
and type = 'insert';

"


dwd_interaction_comment_inc="

insert overwrite table $APP.dwd_interaction_comment_inc partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    chapter_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    comment_txt
from
         (
             select data.id,
                data.user_id,
                data.course_id,
                data.chapter_id,
                FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
                data.comment_txt
         from $APP.ods_comment_info_inc
         where dt = '$do_date'
           and type = 'insert'
             ) oc ;



"
dwd_interaction_process_inc="
insert overwrite table $APP.dwd_interaction_process_inc partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    chapter_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    update_time,
    position_sec
from
(
    select
        data.id,
        data.user_id,
        data.course_id,
        data.chapter_id,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
        data.update_time,
        data.position_sec

    from $APP.ods_user_chapter_process_inc
    where dt='$do_date'
    and type='insert'
)ou;




"
dwd_interaction_exam_inc="


insert overwrite table $APP.dwd_interaction_exam_inc partition(dt='$do_date')
select
    id,
    ot.user_id,
    paper_id,
    score,
    duration_sec,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    submit_time,
    update_time
from
(
    select
        data.id,
        data.user_id,
        data.paper_id,
        data.score,
        data.duration_sec,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
        data.submit_time,
        data.update_time

    from $APP.ods_test_exam_inc
    where dt='$do_date'
    and type='insert'
)ot;

"

dwd_interaction_review_inc="
insert overwrite table $APP.dwd_interaction_review_inc partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    review_txt,
    review_stars
from
(
    select
        data.id,
        data.user_id,
        data.course_id,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
        data.review_txt,
        data.review_stars

    from $APP.ods_review_info_inc
    where dt='$do_date'
    and type='insert'
)orii;

"

dwd_trade_cart_add_inc="

insert overwrite table $APP.dwd_trade_cart_add_inc partition (dt='$do_date')
select
    id,
    user_id,
    course_id,
    date_id,
    create_time,
    source_id,
    source_site,
    source_url
from
(
    select
        data.id,
        data.user_id,
        data.course_id,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time,
        null source_id,
        null source_site,
        null source_url,
        dt date_id
    from $APP.ods_cart_info_inc
    where dt = '$do_date'
    and type = 'insert'
)ocii;

"

dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table $APP.dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
       sc,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.sc,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from $APP.ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;


"

dwd_traffic_start_inc="

set hive.cbo.enable=false;
insert overwrite table $APP.dwd_traffic_start_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
       sc,
    version_code,
    entry,
    open_ad_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time,
    loading_time,
    open_ad_ms,
    open_ad_skip_ms
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.sc,
        common.vc version_code,
        \`start\`.entry,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        ts
    from $APP.ods_log_inc
    where dt='$do_date'
    and \`start\` is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;


"

dwd_traffic_video_view_inc="

set hive.cbo.enable=false;
insert overwrite table $APP.dwd_traffic_video_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
       sc,
    version_code,
    play_sec,
    position_sec,
    video_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
           common.sc,
        common.vc version_code,
        appvideo.play_sec,
        appvideo.position_sec,
        appvideo.video_id,
        ts
    from $APP.ods_log_inc
    where dt='$do_date'
    and appvideo is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;


"

dwd_traffic_action_inc="

 set hive.cbo.enable=false;
insert overwrite table $APP.dwd_traffic_action_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
       sc,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    action_id,
    action_item,
    action_item_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') action_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
           common.sc,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        action.action_id,
        action.item action_item,
        action.item_type action_item_type,
        action.ts
    from $APP.ods_log_inc lateral view explode(actions) tmp as action
    where dt='$do_date'
    and actions is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;


"

dwd_traffic_display_inc="

 set hive.cbo.enable=false;
insert overwrite table $APP.dwd_traffic_display_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
              sc,
    version_code,
    during_time,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') display_time,
    display_type,
    display_item,
    display_item_type,
    display_order,
    display_pos_id
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
           common.sc,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        display.display_type,
        display.item display_item,
        display.item_type display_item_type,
        display.\`order\` display_order,
        display.pos_id display_pos_id,
        ts
    from $APP.ods_log_inc lateral view explode(displays) tmp as display
    where dt='$do_date'
    and displays is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;



"


dwd_traffic_error_inc="




set hive.cbo.enable=false;
set hive.execution.engine=mr;
insert overwrite table $APP.dwd_traffic_error_inc partition(dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
       sc,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    entry,
    loading_time,
    open_ad_id,
    open_ad_ms,
    open_ad_skip_ms,
    actions,
    displays,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') error_time,
    error_code,
    error_msg
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
           common.sc,
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        page.source_type,
        \`start\`.entry,
        \`start\`.loading_time,
        \`start\`.open_ad_id,
        \`start\`.open_ad_ms,
        \`start\`.open_ad_skip_ms,
        actions,
        displays,
        err.error_code,
        err.msg error_msg,
        ts
    from $APP.ods_log_inc
    where dt='$do_date'
    and err is not null
)log
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;




"

dwd_user_login_inc="


 insert overwrite table $APP.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        user_id,
        channel,
        area_code,
        version_code,
        mid_id,
        brand,
        model,
        operate_system,
        ts
    from
    (
        select
            user_id,
            channel,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts,
            row_number() over (partition by session_id order by ts) rn
        from
        (
            select
                user_id,
                channel,
                area_code,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                ts,
                concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
            from
            (
                select
                    common.uid user_id,
                    common.ch channel,
                    common.ar area_code,
                    common.vc version_code,
                    common.mid mid_id,
                    common.ba brand,
                    common.md model,
                    common.os operate_system,
                    ts,
                    if(page.last_page_id is null,ts,null) session_start_point
                from $APP.ods_log_inc
                where dt='$do_date'
                and page is not null
            )t1
        )t2
        where user_id is not null
    )t3
    where rn=1
)t4
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on t4.area_code=bp.province_id;


"
dwd_user_register_inc="

insert overwrite table $APP.dwd_user_register_inc partition(dt='$do_date')
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select
        data.id user_id,
        FROM_UNIXTIME(ts,'yyyy-MM-dd') create_time
    from $APP.ods_user_info_inc
    where dt='$do_date'
    and type='insert'
)ui
right join
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from $APP.ods_log_inc
    where dt='$do_date'
    and common.is_new = 1
    and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(
    select
        id province_id,
        area_code
    from $APP.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;
"




case $1 in

"dwd_trade_order_detail_inc")
hive -e "$dwd_trade_order_detail_inc"
;;
":")
hive -e "$dwd_trade_pay_detail_suc_inc"
;;
"dwd_interaction_favor_add_inc")
hive -e "$dwd_interaction_favor_add_inc"
;;
"dwd_interaction_comment_inc")
hive -e "$dwd_interaction_comment_inc"
;;
"dwd_interaction_process_inc")
hive -e "$dwd_interaction_process_inc"
;;
"dwd_interaction_exam_inc")
hive -e "$dwd_interaction_exam_inc"
;;
"dwd_interaction_review_inc")
hive -e "$dwd_interaction_review_inc"
;;
"dwd_trade_cart_add_inc")
hive -e "$dwd_trade_cart_add_inc"
;;
"dwd_traffic_page_view_inc")
hive -e "$dwd_traffic_page_view_inc"
;;
"dwd_traffic_start_inc")
hive -e "$dwd_traffic_start_inc"
;;
"dwd_traffic_video_view_inc")
hive -e "$dwd_traffic_video_view_inc"
;;
"dwd_traffic_action_inc")
hive -e "$dwd_traffic_action_inc"
;;
"dwd_traffic_display_inc")
hive -e "$dwd_traffic_display_inc"
;;
"dwd_traffic_error_inc")
hive -e "$dwd_traffic_error_inc"
;;
"dwd_user_login_inc")
hive -e "$dwd_user_login_inc"
;;
"dwd_user_register_inc")
hive -e "$dwd_user_register_inc"
;;
"all")

  hive -e "$dwd_trade_order_detail_inc$dwd_trade_pay_detail_suc_inc$dwd_interaction_favor_add_inc$dwd_interaction_comment_inc$dwd_interaction_process_inc$dwd_interaction_exam_inc$dwd_interaction_review_inc$dwd_trade_cart_add_inc$dwd_traffic_page_view_inc$dwd_traffic_start_inc$dwd_traffic_video_view_inc$dwd_traffic_action_inc$dwd_traffic_display_inc$dwd_traffic_error_inc$dwd_user_login_inc$dwd_user_register_inc"
  ;;
  esac
